LOB 表的信息如下：

创建DBLINK：

这边查询总记录数正常。但是当我们查询具体LOB字段里的内容的时候，就会报错，如下：

       在官网的这篇文章中，基于Oracle 8i的进行了说明，报错的原因是因为不支持这个特性。

(A)You cannot actually select a lob column (i.e. CLOB column) from a table

    using remote database link.  Thisis not a supported feature.

(B)Also, these are the INVALID operations on a LOB column:

       --以下操作也不被支持。

    1. SELECT lobcol from table1@remote_site;

    2. INSERT INTO lobtable select type1.lobattr from table1@remote_site;

    3. SELECT dbms_lob.getlength(lobcol) from table1@remote_site;

       在Oracle 9i/10g版本中，存在 ，因此在dblink中使用NVL2 函数时，就会出现ORA-22992的错误。

       该Bug 在Oracle 11gR2中已经修复。

       也可以使用如下方法，来间接的解决这个问题：

Original SQL:

   select nvl2('a', 'b','c' )from ;

Modified SQL:

   selectto_char(nvl2('a','b','c')) from ;

       在这边文章里也提到了原因：because the use ofDBLinks and LOBs via the SELECT from PL/SQL is not supported.

在这篇文章里也提供了一些解决方法：

The followingNotes discuss this issue, and give code snippets as example to work-around therestriction using the INTO clause. Keep in mind, if working with large LOBsizes, it may be best to implement the work-around of using a MaterializedView.

Note (ORA-1406: FetchedColumn Value was Truncated When Selecting Remote Column into Local BLOBVariable) discusses using a Materialized View in the scenarios where the BLOBwill be larger than 2Meg.

Note (ORA-22992 When Trying To SelectLob Columns Over A Database Link) states this feature is not supported but thenat the end of the Note it states starting with 10.2 there is a work-around ofusing LONG and LONG RAW types.

Note (Ora-22992 has a workaround in10gR2) illustrates a work-around (using LONG RAW) as mentioned was possible inthe previous note.

Note (Workaround for ORA-22992)provides 3 work-arounds.

MOS文章：

Ora-22992 has a workaround in 10gR2 [ID 436707.1]

Workaround for ORA-22992 [ID 796282.1]

Starting from 10g the  select from alob object through a database link is supportedby  receiving the LOB objects into variables defined as CHAR orRAW.

--从Oracle10g开始，dblink 的select可以被本地的char或raw 类型变量接收。

（1） Selecting a CLOB objectthrough the dblink:

我这里的测试环境是CBLOB，示例如下：

SQL> declare

2  my_ad varchar(2000);

3  BEGIN

4  SELECT text INTO my_ad FROMlob1@lob_link where rownum=1;

5  dbms_output.put_line(my_ad);

6  END;

/

7 

package STANDARD AUTHIDCURRENT_USER is         -- careful onthis line;

SED edit occurs!

PL/SQL procedure successfully completed.

--这里输出了我们CLOB里的内容。

（2）Selecting a BLOB object through thedblink:

declare my_ad raw(50); BEGIN SELECT obj INTO my_ad FROM test2@torem where id=1; END; /

MOS 文章： ORA-1406: Fetched Column Value was Truncated When Selecting RemoteColumn into Local BLOB Variable [ID 459557.1]

在3.1 中，我们可以使用本地变量来接收dblink中LOB的值，但是这个方法是有限制的。当我们接收的LOB 字段值大于32KB值，就会报如下错误：

ORA-01406 :fetched column value was truncated

"If the LOBis greater than 32KB - 1 in size, then PL/SQL will raise a truncation error andthe contents of the buffer are undefined."

This means thatwe are not able to retrieve BLOBs columns greater than 32KB - 1 in size througha database link.

The restrictionstill holds good for 11g from Oracledocumention  SecureFiles and LargeObjects Developer's Guide

3.2.1 测试LOB字段长度

--在远程端创建过程：

create or replace procedure get_bloblengthas blob_loc blob; blob_length number; begin select into blob_loc from  where name =''; blob_length := dbms_lob.getlength(blob_loc); dbms_output.put_line('Length of the Column : ' || to_char(blob_length)); end;

--在本地调用过程：

exec get_bloblength@repb

如果返回值大于32KB，我们就可以使用物化视图了。

3.2.2 创建物化视图

SQL>

--查询物化视图：CLOB正常显示

SQL> set wrap off;

SQL> select * from lobmv where rownum

     LINE TEXT

-------------------------------------------------------------------------------

        5

        8   type NUMBER is NUMBER_BASE;

        9   subtype FLOAT is NUMBER; --NUMBER(126)

       11   subtype "DOUBLEPRECISION" is FLOAT;

我们在本地创建一张和dblink远程端相同的全局临时表，然后在查询临时表：

-------------------------------------------------------------------------------------------------------

版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!

Skype:  tianlesoftware

QQ:              [email protected]

Email:  

Blog:    

Weibo: 

Twitter:

Facebook:

Linkedin:

-------加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请----

DBA1 群：62697716(满);   DBA2 群：62697977(满)  DBA3 群：62697850(满) 

DBA 超级群：63306533(满);  DBA4 群：83829929   DBA5群： 142216823

DBA6 群：158654907    DBA7 群：172855474   DBA总群：104207940